﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace CNotebook.Purchases
{
    public class APCounterManager:ConnectionManager
    {
        #region Add/Update
        public SqlException AddCounter(APCounterUnit oUnit)
        {
            SqlCommand oCommand = new SqlCommand();
            try
            {
                oCommand.Connection = this.Connection;

                oCommand.CommandText = "INSERT OCOUNTER (DocNum, CardCode, CardName, CounterDate, Remarks, TotalAmount, CreatedBy, DateCreated, ModifiedBy, DateModified, DocStatus) VALUES (@DocNum, @CardCode, @CardName, @CounterDate, @Remarks, @TotalAmount, @CreatedBy, @DateCreated, @ModifiedBy, @DateModified, @DocStatus)";

                oCommand.Parameters.Add(new SqlParameter("@DocNum", oUnit.DocumentNumber));
                oCommand.Parameters.Add(new SqlParameter("@CardCode", oUnit.CardCode));
                oCommand.Parameters.Add(new SqlParameter("@CardName", oUnit.CardName));
                oCommand.Parameters.Add(new SqlParameter("@CounterDate", oUnit.CounterDate));
                oCommand.Parameters.Add(new SqlParameter("@Remarks", oUnit.Remarks));
                oCommand.Parameters.Add(new SqlParameter("@TotalAmount", oUnit.TotalAmount));

                oCommand.Parameters.Add(new SqlParameter("@DocStatus", oUnit.DocStatus));
                oCommand.Parameters.Add(new SqlParameter("@CreatedBy", oUnit.CreatedBy));
                oCommand.Parameters.Add(new SqlParameter("@DateCreated", oUnit.DateCreated));
                oCommand.Parameters.Add(new SqlParameter("@ModifiedBy", oUnit.ModifiedBy));
                oCommand.Parameters.Add(new SqlParameter("@DateModified", oUnit.DateModified));

                oCommand.ExecuteNonQuery();


                foreach (DataRow oRow in oUnit.LineItems.Rows)
                {
                    oCommand = new SqlCommand();

                    oCommand.Connection = this.Connection;

                    oCommand.CommandText = "INSERT INTO COUNTER1 (DocNum, DocType, RefDocument, RefInvoice, RefDocDate, CardCode, CardName, Amount) VALUES (@DocNum, @DocType, @RefDocument, @RefInvoice, @RefDocDate, @CardCode, @CardName, @Amount)";

                    oCommand.Parameters.Add(new SqlParameter("@DocNum", oUnit.DocumentNumber));
                    oCommand.Parameters.Add(new SqlParameter("@DocType", oRow["DocType"].ToString()));
                    oCommand.Parameters.Add(new SqlParameter("@RefDocument", oRow["RefDocument"].ToString()));
                    oCommand.Parameters.Add(new SqlParameter("@RefInvoice", oRow["RefInvoice"].ToString()));
                    oCommand.Parameters.Add(new SqlParameter("@RefDocDate", Convert.ToDateTime(oRow["RefDocDate"].ToString())));

                    oCommand.Parameters.Add(new SqlParameter("@CardCode", Convert.ToString(oRow["CardCode"].ToString())));
                    oCommand.Parameters.Add(new SqlParameter("@CardName", Convert.ToString(oRow["CardName"].ToString())));

                    oCommand.Parameters.Add(new SqlParameter("@Amount", Convert.ToDouble(oRow["Amount"])));


                    oCommand.ExecuteNonQuery();

                }

                return null;
            }
            catch (SqlException ex)
            {
                return ex;
            }
            finally
            {
                oCommand.Dispose();
            }
        }

        public SqlException UpdateCounter(APCounterUnit oUnit)
        {
            SqlCommand oCommand = new SqlCommand();
            try
            {
                oCommand.Connection = this.Connection;

                oCommand.CommandText = "UPDATE OCOUNTER SET CardCode=@CardCode, CardName=@CardName, CounterDate=@CounterDate, Remarks=@Remarks, TotalAmount=@TotalAmount, ModifiedBy=@ModifiedBy, DateModified=@DateModified, DocStatus=@DocStatus WHERE DocNum=@DocNum";

                oCommand.Parameters.Add(new SqlParameter("@DocNum", oUnit.DocumentNumber));
                oCommand.Parameters.Add(new SqlParameter("@CardCode", oUnit.CardCode));
                oCommand.Parameters.Add(new SqlParameter("@CardName", oUnit.CardName));
                oCommand.Parameters.Add(new SqlParameter("@CounterDate", oUnit.CounterDate));
                oCommand.Parameters.Add(new SqlParameter("@Remarks", oUnit.Remarks));
                oCommand.Parameters.Add(new SqlParameter("@TotalAmount", oUnit.TotalAmount));

                oCommand.Parameters.Add(new SqlParameter("@DocStatus", oUnit.DocStatus));
                oCommand.Parameters.Add(new SqlParameter("@CreatedBy", oUnit.CreatedBy));
                oCommand.Parameters.Add(new SqlParameter("@DateCreated", oUnit.DateCreated));
                oCommand.Parameters.Add(new SqlParameter("@ModifiedBy", oUnit.ModifiedBy));
                oCommand.Parameters.Add(new SqlParameter("@DateModified", oUnit.DateModified));

                oCommand.ExecuteNonQuery();

                oCommand = new SqlCommand();
                oCommand.Connection = this.Connection;
                oCommand.CommandText = "DELETE FROM COUNTER1 WHERE DocNum=@DocNum";
                oCommand.Parameters.Add(new SqlParameter("@DocNum", oUnit.DocumentNumber));

                foreach (DataRow oRow in oUnit.LineItems.Rows)
                {
                    if (oRow.RowState != DataRowState.Deleted)
                    {
                        oCommand = new SqlCommand();

                        oCommand.Connection = this.Connection;

                        oCommand.CommandText = "INSERT INTO COUNTER1 (DocNum, DocType, RefDocument, RefInvoice, RefDocDate, CardCode, CardName, Amount) VALUES (@DocNum, @DocType, @RefDocument, @RefInvoice, @RefDocDate, @CardCode, @CardName, @Amount)";

                        oCommand.Parameters.Add(new SqlParameter("@DocNum", oUnit.DocumentNumber));
                        oCommand.Parameters.Add(new SqlParameter("@DocType", oRow["DocType"].ToString()));
                        oCommand.Parameters.Add(new SqlParameter("@RefDocument", oRow["RefDocument"].ToString()));
                        oCommand.Parameters.Add(new SqlParameter("@RefInvoice", oRow["RefInvoice"].ToString()));
                        oCommand.Parameters.Add(new SqlParameter("@RefDocDate", Convert.ToDateTime(oRow["RefDocDate"].ToString())));

                        oCommand.Parameters.Add(new SqlParameter("@CardCode", Convert.ToString(oRow["CardCode"].ToString())));
                        oCommand.Parameters.Add(new SqlParameter("@CardName", Convert.ToString(oRow["CardName"].ToString())));

                        oCommand.Parameters.Add(new SqlParameter("@Amount", Convert.ToDouble(oRow["Amount"])));


                        oCommand.ExecuteNonQuery();
                    }

                }

                return null;
            }
            catch (SqlException ex)
            {
                return ex;
            }
            finally { oCommand.Dispose(); }
        }
        #endregion

        #region Updating Status

        public void CloseDocument(string _DOC_NUMBER)
        {
            SqlCommand oCommand = new SqlCommand();

            oCommand.Connection = this.Connection;

            oCommand.CommandText = "UPDATE OCOUNTER SET DocStatus='Closed' WHERE DocNum=@DocNum";
            oCommand.Parameters.Add(new SqlParameter("@DocNum", _DOC_NUMBER));
            oCommand.ExecuteNonQuery();
        }


        public void CancelDocument(string _DOC_NUMBER)
        {
            SqlCommand oCommand = new SqlCommand();

            oCommand.Connection = this.Connection;


            oCommand.CommandText = "UPDATE OCOUNTER SET DocStatus='Canceled' WHERE DocNum=@DocNum";
            oCommand.Parameters.Add(new SqlParameter("@DocNum", _DOC_NUMBER));
            oCommand.ExecuteNonQuery();
        }


        public void PostDocument(string _DOC_NUMBER)
        {
            SqlCommand oCommand = new SqlCommand();

            oCommand.Connection = this.Connection;


            oCommand.CommandText = "UPDATE OCOUNTER SET DocStatus='Posted' WHERE DocNum=@DocNum";
            oCommand.Parameters.Add(new SqlParameter("@DocNum", _DOC_NUMBER));
            oCommand.ExecuteNonQuery();




        }




        #endregion

        #region Retrieving Data

        public DataTable GetCounterDocument(string _DOC_NUMBER)
        {
            DataTable dt = new DataTable();

            SqlCommand oCommand = new SqlCommand();
            SqlDataAdapter oAdapter = new SqlDataAdapter();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT * FROM OCOUNTER WHERE DocNum=@DocNum";
            oCommand.Parameters.Add(new SqlParameter("@DocNum", _DOC_NUMBER));

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(dt);

            return dt;
        }

        public DataTable GetCounterDocumentsByDocNum(string _DOC_NUMBER)
        {
            DataTable dt = new DataTable();

            SqlCommand oCommand = new SqlCommand();
            SqlDataAdapter oAdapter = new SqlDataAdapter();

            _DOC_NUMBER = "%" + _DOC_NUMBER + "%";

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT * FROM OCOUNTER WHERE DocNum Like @DocNum";
            oCommand.Parameters.Add(new SqlParameter("@DocNum", _DOC_NUMBER));

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(dt);

            return dt;
        }

        public DataTable GetCounterDocumentsByVendor(string _VENDOR_NAME)
        {
            DataTable dt = new DataTable();

            SqlCommand oCommand = new SqlCommand();
            SqlDataAdapter oAdapter = new SqlDataAdapter();

            _VENDOR_NAME = "%" + _VENDOR_NAME + "%";

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT * FROM OCOUNTER WHERE CardName Like @CardName";
            oCommand.Parameters.Add(new SqlParameter("@CardName", _VENDOR_NAME));

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(dt);

            return dt;
        }


        public DataTable GetPostedCounterByDocNum(string _DOC_NUMBER)
        {
            DataTable dt = new DataTable();

            SqlCommand oCommand = new SqlCommand();
            SqlDataAdapter oAdapter = new SqlDataAdapter();

            _DOC_NUMBER = "%" + _DOC_NUMBER + "%";

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT * FROM OCOUNTER WHERE DocNum Like @DocNum and DocStatus='Posted'";
            oCommand.Parameters.Add(new SqlParameter("@DocNum", _DOC_NUMBER));


            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(dt);

            return dt;
        }

        public DataTable GetPostedCounterByVendor(string _VENDOR_NAME)
        {
            DataTable dt = new DataTable();

            SqlCommand oCommand = new SqlCommand();
            SqlDataAdapter oAdapter = new SqlDataAdapter();

            _VENDOR_NAME = "%" + _VENDOR_NAME + "%";

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT * FROM OCOUNTER WHERE CardName Like @CardName and DocStatus='Posted'";
            oCommand.Parameters.Add(new SqlParameter("@CardName", _VENDOR_NAME));

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(dt);

            return dt;
        }


        public DataTable GetCounterDocumentLineItems(string _DOC_NUMBER)
        {
            DataTable dt = new DataTable();

            SqlCommand oCommand = new SqlCommand();
            SqlDataAdapter oAdapter = new SqlDataAdapter();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT * FROM COUNTER1 WHERE DocNum=@DocNum";
            oCommand.Parameters.Add(new SqlParameter("@DocNum", _DOC_NUMBER));

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(dt);

            return dt;
        }

        #endregion
    }
}
